# -*- coding:utf-8 -*-

import os, sys, configparser

from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy import create_engine, func
from sqlalchemy import and_, or_
from functools import wraps

sys.path.append(os.path.realpath(os.path.join(os.path.dirname(__file__), "../..")))

from exchange.conf.conf import conf



class mysql(object):

    __instance = None
    __engine = None

    def __init__(self):
        self.db_host = conf.mysql.host
        self.db_port = conf.mysql.port
        self.user = conf.mysql.user
        self.password = conf.mysql.password
        self.db = conf.mysql.db
        self.charset = conf.mysql.charset

    def __new__(cls, *args, **kwargs):
        if not cls.__instance:
            cls.__instance = object.__new__(cls)
        return cls.__instance

    def gen_engine(self):
        if not mysql.__engine:
            engine = create_engine("mysql+pymysql://{username}:{password}@{server}/{database}?charset={charset}"
                                   .format(username=self.user,
                                           password=self.password,
                                           server=self.db_host,
                                           database=self.db,
                                           charset=self.charset),
                                   pool_size=100,                               # 连接池大小
                                   max_overflow=100,                            # 超过连接池大小外最多创建的连接
                                   pool_recycle=2,                              # 多久之后对线程池中的线程进行一次连接的回收（重置）
                                   pool_timeout=30,                             # 池中没有线程最多等待的时间，否则报错
                                   echo=False)
            engine.execute("SET NAMES {charset};".format(charset=self.charset))
            mysql.__engine = engine
        return mysql.__engine

    @property
    def mysql_session(self):
        self.gen_engine()
        mysql_db = sessionmaker(bind=mysql.__engine)
        return mysql_db()



# 类化装饰器
class mysql_session:
    def __call__(self, func):
        @wraps(func)
        def wrapper(*args, **kwargs):
            session = mysql().mysql_session
            result = func(*args, session, **kwargs)
            session.close()
            return result
        return wrapper



class model_base(object):

    # class to dict
    def to_dict(self):
        self.__dict__.pop("_sa_instance_state")
        return self.__dict__


    """
    total
    usage:
        count = admins.total()
    """
    @classmethod
    @mysql_session()
    def total(cls, session):
        count = session.query(func.count(cls.id)).scalar()
        return count


    """
    count
    usage:
        count = admins.count(1 == 1)                                            --- total count
        count = admins.count(and_())                                            --- total count
        count = admins.count({"name": "abc"})                                   --- count by query
    """
    @classmethod
    @mysql_session()
    def count(cls, params, session):
        count = session.query(func.count(cls.id)).filter(params).scalar()
        return count


    """
    find for page
    usage:
        data = admins.find(1, 10, 1 == 1)                                       --- find all for page
        data = admins.find(1, 10, and_())                                       --- find all for page
        data = admins.find(1, 10, admins.id > 0)                                --- find by query for page
        data = admins.find(1, 20,
            and_(admins.id > 0, admins.username == "abc"))                      --- find by query for page
    """
    @classmethod
    @mysql_session()
    def find(cls, page_number, page_size, params, session):
        offset = 0 if page_number < 1 else (page_number - 1) * page_size
        limit = 0 if page_size < 0 else page_size
        data = session.query(cls).filter(params).offset(offset).limit(limit).all()
        count = session.query(func.count(cls.id)).filter(params).scalar()
        page_count = int(count / limit) + (1 if count % limit > 0 else 0)
        data = [o.to_dict() for o in data]
        page = {
            "page_number":  page_number,
            "page_size":    page_size,
            "row_count":    len(data),
            "page_count":   page_count,
            "total_count":  count
        }
        return data, page


    """"
    get one
    usage:
        obj = admins.one(admins.id == 2)                                        --- get one by id
        obj = admins.one(and_(admins.id == 2, admins.username == "abc"))        --- get one by query
    """
    @classmethod
    @mysql_session()
    def one(cls, params, session):
        o = session.query(cls).filter(params).first()
        if o:
            return o.to_dict()
        return o


    """
    all
    usage:
        count = admins.all()                                                    --- get all data
    """
    @classmethod
    @mysql_session()
    def all(cls, session):
        data = session.query(cls).all()
        data = [o.to_dict() for o in data]
        return data


    """
    select data
    usage:
        data = admins.select(1 == 1)                                            --- select all
        data = admins.select(and_())                                            --- select all
        data = admins.select(admins.id == 1)                                    --- select by query
        data = admins.select(and_(admins.id == 1, admins.status == 1))          --- select by query
    """
    @classmethod
    @mysql_session()
    def select(cls, params, session):
        data = session.query(cls).filter(params).all()
        data = [o.to_dict() for o in data]
        return data


    """
    insert one
    usage:
        admins.insert(username = "abc", password = "test")                      --- insert one
    """
    @classmethod
    @mysql_session()
    def insert(cls, session, **params):
        entity = cls(**params)
        session.add(entity)
        session.flush()
        id = entity.to_dict()["id"]
        session.commit()
        return id


    """
    update
    usage:
        admins.update({"name": "test"}, 1 == 1)                                 --- update all
        admins.update({"name": "test"}, and_())                                 --- update all
        admins.update({"name": "test"}, admins.id == 1)                         --- update by query
        admins.update({"name": "test"},
            and_(admins.id > 1, admins.username == "abc"))                      --- update by query
    """
    @classmethod
    @mysql_session()
    def update(cls, obj, params, session):
        session.query(cls).filter(params).update(obj)
        session.commit()


    """
    delete
    usage:
        admins.delete(1 == 1)                                                   --- delete all
        admins.delete(and_())                                                   --- delete all
        admins.delete(admins.id > 1)                                            --- delete by query
        admins.delete(and_(admins.id > 1, admins.username == "abc"))            --- delete by query
    """
    @classmethod
    @mysql_session()
    def delete(cls, params, session):
        session.query(cls).filter(params).delete()
        session.commit()
